<?php

class DB {

  public $db;
  public $select;

  public function __construct ($host, $dbname, $user, $password)
  {
    $this->connect ($host, $dbname, $user, $password);
  }

  private function connect ($host, $dbname, $user, $password)
    {
      $this->db = new PDO('mysql:host='.$host.';dbname='.$dbname,$user,$password);
      if(empty($this->db))
      {
        echo 'Erreur : Impossible de se connecter à la base de données';
        exit();
      }
    }
  
  public function initialize ()
  {
    $file = '../SQL/base_mysql.sql';
    $req = fread(fopen($file, "r"), filesize($file));
    $this->db->prepare($req)->execute();
  }

  public function fill ()
  {
    $file = '../SQL/donnees_mysql.sql';
    $req = fread(fopen($file, "r"), filesize($file));
    $this->db->prepare($req)->execute();
  }

  public function delete ()
  {
    $file = '../SQL/suppression_mysql.sql';
    $req = fread(fopen($file, "r"), filesize($file));
    $this->db->prepare($req)->execute();
  }


  public function prepare ()
  {
    $this->SELECT = $this->db->prepare('SELECT * from HISTOIRE;');
    $this->SELECT->setFetchMode(PDO::FETCH_ASSOC);
  }

  public function getCollectionName ()
  {
    $tmp = $this->db->prepare('SELECT NUMERO_COLLECTION,NOM_COLLECTION from COLLECTION;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getEditorName ()
  {
    $tmp = $this->db->prepare('SELECT NUMERO_EDITEUR,NOM_EDITEUR from EDITEUR;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getAuteurName ()
  {
    $tmp = $this->db->prepare('SELECT NUMERO_AUTEUR,NOM_AUTEUR from AUTEUR;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getSerieName ()
  {
    $tmp = $this->db->prepare('SELECT NUMERO_SERIE,NOM_SERIE from SERIE;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getAlbum (){
    $tmp = $this->db->prepare('SELECT * from ALBUM;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getVolume (){
    $tmp = $this->db->prepare('SELECT * from VOLUME;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }
  public function getEditeur (){
    $tmp = $this->db->prepare('SELECT * from EDITEUR;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }
  public function getAuteur (){
    $tmp = $this->db->prepare('SELECT * from AUTEUR;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getCollection (){
    $tmp = $this->db->prepare('SELECT * from COLLECTION;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getHistoire (){
    $tmp = $this->db->prepare('SELECT * from HISTOIRE;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getRevue (){
    $tmp = $this->db->prepare('SELECT * from REVUE;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getSerie (){
    $tmp = $this->db->prepare('SELECT * from SERIE;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getDessine (){
    $tmp = $this->db->prepare('SELECT * from DESSINE;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function addDessine($num_his,$num_aut){
    $this->db->exec("INSERT into DESSINE values ($num_his, $num_aut);");
    $this->db->exec('COMMIT;');
  }

  public function deleteDessine($num_his,$num_aut){
    $this->db->exec("DELETE from DESSINE where NUMERO_HISTOIRE=$num_his and NUMERO_AUTEUR=$num_aut;");
    $this->db->exec('COMMIT;');
  }

  public function getScenarise (){
    $tmp = $this->db->prepare('SELECT * from SCENARISE;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function addScenarise($num_his,$num_aut){
    $this->db->exec("INSERT into SCENARISE values ($num_his, $num_aut);");
    $this->db->exec('COMMIT;');
  }

  public function deleteScenarise($num_his,$num_aut){
    $this->db->exec("DELETE from SCENARISE where NUMERO_HISTOIRE=$num_his and NUMERO_AUTEUR=$num_aut;");
    $this->db->exec('COMMIT;');
  }

  public function getApparaitre (){
    $tmp = $this->db->prepare('SELECT * from APPARAITRE;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function addApparaitre($num_his,$num_vol,$annot){
    $this->db->exec("INSERT into APPARAITRE values ($num_vol, $num_his, '$annot');");
    $this->db->exec('COMMIT;');
  }

  public function deleteApparaitre($num_his,$num_vol){
    $this->db->exec("DELETE from APPARAITRE where NUMERO_HISTOIRE=$num_his and NUMERO_VOLUME=$num_vol;");
    $this->db->exec('COMMIT;');
  }

  public function getVolumeName (){
    $tmp = $this->db->prepare('SELECT NUMERO_VOLUME,TITRE_VOLUME from VOLUME;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getVolumeNumber (){
    $tmp = $this->db->prepare('SELECT TITRE_VOLUME,NUMERO_VOLUME from VOLUME;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getHistoireName (){
    $tmp = $this->db->prepare('SELECT NUMERO_HISTOIRE,NOM_HISTOIRE from HISTOIRE;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getHistoireNumber (){
    $tmp = $this->db->prepare('SELECT NOM_HISTOIRE,NUMERO_HISTOIRE from HISTOIRE;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getVolumeSeulName (){
    $tmp = $this->db->prepare('SELECT NUMERO_VOLUME,TITRE_VOLUME from VOLUME where NUMERO_VOLUME not in (SELECT NUMERO_VOLUME from ALBUM) and NUMERO_VOLUME not in (SELECT NUMERO_VOLUME from REVUE);');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }


  public function getAlbumName (){
    $tmp = $this->db->prepare('SELECT ALBUM.NUMERO_VOLUME, TITRE_VOLUME from ALBUM inner join VOLUME
on ALBUM.NUMERO_VOLUME = VOLUME.NUMERO_VOLUME;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function addAlbum($num_vol,$num_col, $num_dans_col){
    $this->db->exec("INSERT into ALBUM values ($num_vol, $num_col, $num_dans_col);");
    $this->db->exec('COMMIT;');
  }

  public function deleteAlbum($numero_volume){
    $this->db->exec("DELETE from ALBUM where NUMERO_VOLUME=$numero_volume;");
    $this->db->exec('COMMIT;');
  }

  public function majAlbum($numero_volume, $numero_collection, $numero_dans_collection){
    $this->db->exec("UPDATE ALBUM set NUMERO_COLLECTION=$numero_collection, NUMERO_DANS_COLLECTION=$numero_dans_collection where NUMERO_VOLUME=$numero_volume;");
    $this->db->exec('COMMIT;');
  }

  public function addVolume($titre,$num_edit,$date_edit){
    $this->db->exec("INSERT into VOLUME values ('', '$titre', $num_edit, '$date_edit');");
    $this->db->exec('COMMIT;');
  }

  public function addEditeur($nom_edit){
    $this->db->exec("INSERT into EDITEUR values ('', '$nom_edit');");
    $this->db->exec('COMMIT;');
  
  }

  public function addCollection($nom_col){
    $this->db->exec("INSERT into COLLECTION values ('', '$nom_col');");
    $this->db->exec('COMMIT;');
  
  }

  public function addAuteur($nom_auteur){
    $this->db->exec("INSERT into AUTEUR values ('', '$nom_auteur');");
    $this->db->exec('COMMIT;');
  }

  public function addHistoire($nom_hist,$num_dans_serie,$num_serie,$annee){
    $this->db->exec("INSERT into HISTOIRE values ('','$nom_hist', '$annee', $num_serie, $num_dans_serie);");
    $this->db->exec('COMMIT;');
  }

  public function addRevue($num_revue, $num_vol){
    $this->db->exec("INSERT into REVUE values ($num_revue,$num_vol);");
    $this->db->exec('COMMIT;');
  }

  public function deleteAuteur($numero_auteur){
    $this->db->exec("DELETE from AUTEUR where NUMERO_AUTEUR=$numero_auteur;");
    $this->db->exec('COMMIT;');
  }

  public function majAuteur($numero_auteur, $nom_auteur){
    $this->db->exec("UPDATE AUTEUR set NOM_AUTEUR='$nom_auteur' where NUMERO_AUTEUR=$numero_auteur;");
    $this->db->exec('COMMIT;');
  }

  public function addSerie($nom_serie){
    $this->db->exec("INSERT into SERIE values ('', '$nom_serie');");
    $this->db->exec('COMMIT;');
  }

  public function deleteSerie($numero_serie){
    $this->db->exec("DELETE from SERIE where NUMERO_SERIE=$numero_serie;");
    $this->db->exec('COMMIT;');
  }

  public function majSerie($numero_serie, $nom_serie){
    $this->db->exec("UPDATE SERIE set NOM_SERIE='$nom_serie' where NUMERO_SERIE=$numero_serie;");
    $this->db->exec('COMMIT;');
  }

  public function deleteVolume($numero_volume){
    $this->db->exec("DELETE from VOLUME where NUMERO_VOLUME=$numero_volume;");
    $this->db->exec('COMMIT;');
  }

  public function majVolume($numero_volume, $nom_volume, $numero_editeur, $annee_edition){
    $this->db->exec("UPDATE VOLUME set TITRE_VOLUME='$nom_volume', NUMERO_EDITEUR=$numero_editeur, ANNEE_EDITION='$annee_edition' where NUMERO_VOLUME=$numero_volume;");
    $this->db->exec('COMMIT;');
  }

  public function majEditeur($numero_editeur, $nom_editeur){
    $this->db->exec("UPDATE EDITEUR set NOM_EDITEUR='$nom_editeur' where NUMERO_EDITEUR=$numero_editeur;");
    $this->db->exec('COMMIT;');
  }

  public function deleteEditeur($numero_editeur){
    $this->db->exec("DELETE from EDITEUR where NUMERO_EDITEUR=$numero_editeur;");
    $this->db->exec('COMMIT;');
  }

  public function getEditeurName ()
  {
    $tmp = $this->db->prepare('SELECT NUMERO_EDITEUR,NOM_EDITEUR from EDITEUR;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }
  public function deleteCollection($numero_collection){
    $this->db->exec("DELETE from COLLECTION where NUMERO_COLLECTION=$numero_collection;");
    $this->db->exec('COMMIT;');
  }
  public function majCollection($numero_collection, $nom_collection){
    $this->db->exec("UPDATE COLLECTION set NOM_COLLECTION='$nom_collection' where NUMERO_COLLECTION=$numero_collection;");
    $this->db->exec('COMMIT;');
  }
  public function getRevueName()
  {
    $tmp = $this->db->prepare('SELECT REVUE.NUMERO_VOLUME, TITRE_VOLUME from REVUE inner join VOLUME
on REVUE.NUMERO_VOLUME = VOLUME.NUMERO_VOLUME;');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function getRevueNumber()
  {
    $tmp = $this->db->prepare('SELECT TITRE_VOLUME, NUMERO_VOLUME from VOLUME where NUMERO_VOLUME in (SELECT NUMERO_VOLUME from REVUE);');
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function deleteRevue($numero_volume){
    $this->db->exec("DELETE from REVUE where NUMERO_VOLUME=$numero_volume;");
    $this->db->exec('COMMIT;');
  }

  public function majRevue($numero_volume, $numero_revue){
    $this->db->exec("UPDATE REVUE set NUMERO_REVUE=$numero_revue where NUMERO_VOLUME=$numero_volume;");
    $this->db->exec('COMMIT;');
  }

  public function deleteHistoire($numero_histoire){
    $this->db->exec("DELETE from HISTOIRE where NUMERO_HISTOIRE=$numero_histoire;");
    $this->db->exec('COMMIT;');
  }

  public function majHistoire($num_hist, $nom_hist,$num_dans_serie,$num_serie,$annee){
    $this->db->exec("UPDATE HISTOIRE set NOM_HISTOIRE='$nom_hist', ANNEE_PARUTION='$annee', NUMERO_SERIE=$num_serie, NUMERO_DANS_SERIE=$num_dans_serie where NUMERO_HISTOIRE=$num_hist;");
    $this->db->exec('COMMIT;');
  }

  /** ----- REQUETES ------- **/

  public function infoAlbum($num_volume){
    $tmp = $this->db->prepare("select R3.NUMERO_VOLUME, TITRE_VOLUME, NOM_COLLECTION, NUMERO_DANS_COLLECTION from
((select C.NOM_COLLECTION, C.NUMERO_COLLECTION from COLLECTION C) as R1
	 right outer join
	       ((select A.NUMERO_COLLECTION, A.NUMERO_VOLUME, A.NUMERO_DANS_COLLECTION from ALBUM A) as R2
	       		inner join
			      (select V.NUMERO_VOLUME, V.TITRE_VOLUME from VOLUME V
			      where V.NUMERO_VOLUME = $num_volume) as R3 -- Paramètre à changer : numéro volume
			on R3.NUMERO_VOLUME = R2.NUMERO_VOLUME)
		on R2.NUMERO_COLLECTION = R1.NUMERO_COLLECTION);");
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function bioAuteur ($num_auteur){
    $tmp = $this->db->prepare("select NOM_SERIE, NOM_HISTOIRE, ANNEE_PARUTION
from SERIE right outer join 
	(select * from HISTOIRE where NUMERO_HISTOIRE in 
		(select NUMERO_HISTOIRE from ((select * from DESSINE) union (select * from SCENARISE)) as DS 
			where DS.NUMERO_AUTEUR=$num_auteur)) as H
on SERIE.NUMERO_SERIE = H.NUMERO_SERIE
order by ANNEE_PARUTION asc;");
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

  public function listeAuteurRevuePeriode ($titre_volume,$date_debut,$date_fin){
    $tmp = $this->db->prepare("select A.NUMERO_AUTEUR, A.NOM_AUTEUR, count(*) as NB_NUMEROS from AUTEUR as A
inner join
(((select * from DESSINE) union distinct (select * from SCENARISE)) as R1
  inner join 
    (select NUMERO_VOLUME,NUMERO_HISTOIRE 
      from APPARAITRE
	where NUMERO_VOLUME in 
          (select NUMERO_VOLUME from VOLUME
	    where NUMERO_VOLUME in (select NUMERO_VOLUME from REVUE)
	      and TITRE_VOLUME='$titre_volume'
	      and ANNEE_EDITION>'$date_debut'
              and ANNEE_EDITION<'$date_fin')) as R2
	on R1.NUMERO_HISTOIRE = R2.NUMERO_HISTOIRE)
	on A.NUMERO_AUTEUR = R1.NUMERO_AUTEUR
group by A.NOM_AUTEUR
order by NOM_AUTEUR asc;");
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();

  }

  public function historiqueHistoire ($nom_histoire)
  {  
    $tmp = $this->db->prepare("select ANNEE_EDITION, TITRE_VOLUME, NOM_EDITEUR
from (EDITEUR inner join 
(select * from VOLUME 
	where NUMERO_VOLUME in 
	(SELECT NUMERO_VOLUME from APPARAITRE
		where NUMERO_HISTOIRE in 
		(SELECT NUMERO_HISTOIRE from HISTOIRE
			where NOM_HISTOIRE = '$nom_histoire'))) as V
on EDITEUR.NUMERO_EDITEUR = V.NUMERO_EDITEUR)
order by ANNEE_EDITION asc;
");
  $tmp->setFetchMode(PDO::FETCH_ASSOC);
  $tmp->execute();
  return $tmp->fetchAll();
  }

  public function nbHisAut ($num_aut)
  {
    $tmp = $this->db->prepare("select count(NUMERO_HISTOIRE) as NB_HISTOIRES from  
((select NUMERO_HISTOIRE, NUMERO_AUTEUR from DESSINE where NUMERO_AUTEUR=$num_aut) 
union distinct 
(select NUMERO_HISTOIRE, NUMERO_AUTEUR from SCENARISE where NUMERO_AUTEUR=$num_aut)) as R1;
");
  $tmp->setFetchMode(PDO::FETCH_ASSOC);
  $tmp->execute();
  return $tmp->fetchAll();
  }

  public function serieMaxAut ()
  {
    $tmp = $this->db->prepare("select NUMERO_SERIE, max(NB_AUTEURS) as MAX_NB_AUTEURS from (
select NUMERO_SERIE, count(*) as NB_AUTEURS
from (HISTOIRE inner join ((select * from SCENARISE) union distinct (select * from DESSINE))as R1 
				on HISTOIRE.NUMERO_HISTOIRE = R1.NUMERO_HISTOIRE)
where NUMERO_SERIE is not NULL
group by NUMERO_SERIE) as R2;");
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll();
  }

    public function hisNbAppAlb ()
  {
    $tmp = $this->db->prepare("select R1.NUMERO_HISTOIRE, NOM_HISTOIRE, count(*) as NB_APPARITIONS from
(HISTOIRE inner join (select * from APPARAITRE where NUMERO_VOLUME in (select NUMERO_VOLUME from ALBUM)) as R1
			on HISTOIRE.NUMERO_HISTOIRE=R1.NUMERO_HISTOIRE)
group by R1.NUMERO_HISTOIRE;");
    $tmp->setFetchMode(PDO::FETCH_ASSOC);
    $tmp->execute();
    return $tmp->fetchAll(); 
  }
    
    public function nbMoyAut ($titre_vol,$annee_min,$annee_max)
    {
      $tmp = $this->db->prepare("select avg(NB_AUTEURS) as NB_AUTEURS_MOY from(
select count(*) as NB_AUTEURS,R4.NUMERO_VOLUME from
(((select * from SCENARISE) union distinct (select * from DESSINE)) as R3
inner join
((select NUMERO_HISTOIRE, NUMERO_VOLUME from APPARAITRE) as R4
inner join
(select NUMERO_VOLUME from VOLUME
      where NUMERO_VOLUME in (select NUMERO_VOLUME from REVUE)
      and TITRE_VOLUME='$titre_vol'
      and ANNEE_EDITION>'$annee_min'
      and ANNEE_EDITION<'$annee_max') as R2
on R2.NUMERO_VOLUME=R4.NUMERO_VOLUME) 
on R4.NUMERO_HISTOIRE=R3.NUMERO_HISTOIRE)
group by R4.NUMERO_VOLUME) as R1
;");  
      $tmp->setFetchMode(PDO::FETCH_ASSOC);
      $tmp->execute();
      return $tmp->fetchAll(); 
    }
}

?>
